/*
 EXEC sp_load_bom_fg_lots -1, 1568
*/
CREATE PROCEDURE sp_load_bom_fg_lots ( @assembly_release_id INT, @assembly_process_step_id INT )
AS
BEGIN
	DECLARE @lot_id				INT
	DECLARE @lot_number			VARCHAR(50)
	DECLARE @wafer_id			INT
	DECLARE @available_quantity INT
	DECLARE @pulled_quantity	INT
	DECLARE @bom_process_step_id INT
	DECLARE @fg_process_step_id INT
	DECLARE @is_selected		INT	-- 0 means Selected Lot.
	DECLARE @Bom_Part_Number VARCHAR(50)

	DECLARE @lot_cursor CURSOR
	DECLARE @results TABLE ( Is_Selected INT, Lot_Id INT, Lot_Number VARCHAR(50), Available_Qty INT, Pulled_Qty INT, Bom_Part_Number VARCHAR(50))

	SET @available_quantity = 0
	SET @pulled_quantity	= 0	

	--SELECT @fg_process_step_id = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @assembly_process_step_id, 1 ) WHERE PS_TYPE_NAME = 'Finished Goods'
	SELECT @bom_process_step_id = Bom_ProcessStep_Id FROM ASSEMBLY_BOM_SETUP_TABLE WHERE Assembly_ProcessStep_Id = @assembly_process_step_id

	--For Getting The Lots Available for The BOM Part Number	
	SET @lot_cursor = CURSOR FOR SELECT L.Lot_ID, LOT_Number, ISNULL( I.IL_Qty_Balance, Qty_In ), Part_Number FROM Lot_Table L, Inventory_Lot I, Product P WHERE  L.Lot_Id = I.Lot_Id and P.Processstep_Id = L.Processstep_Id AND L.Processstep_Id = @bom_process_step_id ORDER BY L.Lot_ID	
	OPEN @lot_cursor
	FETCH NEXT FROM @lot_cursor INTO @lot_id, @lot_Number, @available_quantity, @Bom_Part_Number	
	WHILE @@FETCh_STATUS = 0
	BEGIN	

		IF ( @lot_id <> -1 AND @lot_id <> 0 )
		BEGIN

			--SET @available_quantity = 0
			SET @pulled_quantity = 0
			SET @is_selected = 1

			--For Getting The Pulled Quantity.
			IF ( @assembly_release_id IS NOT NULL AND @assembly_release_id > 0 )
			BEGIN
				SELECT @pulled_quantity = ISNULL( Qty_Pulled, 0 ) FROM BOM_RELEASE_LOT_TABLE WHERE Assembly_Release_Id = @assembly_release_id AND LOT_ID = @lot_id
				IF ( @pulled_quantity IS NOT NULL AND @pulled_quantity > 0 ) --Added Quantity Pulled to Quantity Available
				BEGIN
					SET @available_quantity = ISNULL( @available_quantity, 0 ) + @pulled_quantity
					SET @is_selected = 0
				END
			END

			--print ' CHECK ISNULL @available_quantity =  ' + convert(varchar, ISNULL( @available_quantity, '-111' ) )

			IF ( ( @available_quantity IS NOT NULL AND @available_quantity > 0 ) )
			BEGIN
				INSERT INTO @results VALUES ( @is_selected, @lot_id, @lot_Number, @available_quantity, @pulled_quantity, @Bom_Part_Number )
			END

			FETCH NEXT FROM @lot_cursor INTO @lot_id, @lot_Number, @available_quantity, @Bom_Part_Number	
		END
	END
	CLOSE @lot_cursor
	DEALLOCATE @lot_cursor

	SELECT * FROM @results

END